/*********************************************************************************
 *      Copyright:  (C) Guo Zixuan
 *                  All rights reserved.
 *
 *       Filename:  server_sql.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(09/04/25)
 *         Author:  Guo Zixuan <guozixuan@gmail.com>
 *      ChangeLog:  1, Release initial version on "09/04/25 15:32:21"
 *                 
 ********************************************************************************/
#include "client_sql.h"

int sqlite_init(sqlite3 **db, const char *db_name)
{
	int rc = sqlite3_open(db_name, db);

	if (rc != SQLITE_OK)
	{
		log_error("Cannot open database: %s\n", sqlite3_errmsg(*db));
		return -1;
	}
	log_info("Database opened successfully.\n");
	
	char *sql = "CREATE TABLE IF NOT EXISTS clienttable ("
				"id INTEGER PRIMARY KEY AUTOINCREMENT, "
				"data TEXT NOT NULL); ";
	char *err_msg = NULL;
	rc = sqlite3_exec(*db, sql, NULL, NULL, &err_msg);
	
	if (rc != SQLITE_OK)
	{
		log_error("SQL error: %s\n", err_msg);
		sqlite3_free(err_msg);
		sqlite3_close(*db);
		return -1;
	}
	log_info("Table created successfully.\n");
	return 0;
}

int sqlite_insert_data(sqlite3 *db, const char *package) 
{
	char sql[512];
	snprintf(sql, sizeof(sql), "INSERT INTO clienttable (data) VALUES ('%s');", package);

	char *err_msg = NULL;
	int rc = sqlite3_exec(db, sql, NULL, NULL, &err_msg);
	if (rc != SQLITE_OK)
	{
		log_error("Insert failed: %s\n", err_msg);
		sqlite3_free(err_msg);
		return -1;
	}

	//log_info("Data insert to sqlite successfully.\n");
	return 0;
}

int sqlite_read_first_data(sqlite3 *db, char *buf, size_t buflen)
{
	const char *sql = "SELECT data FROM clienttable ORDER BY id ASC LIMIT 1;";
	sqlite3_stmt *stmt = NULL;
	int						rc;
	size_t					text_len;
	const unsigned char    *text;

	// 准备 SQL 语句
	if (sqlite3_prepare_v2(db, sql, -1, &stmt, NULL) != SQLITE_OK)
	{
		log_error("Failed to prepare statement: %s", sqlite3_errmsg(db));
		return -1;
	}

	// 执行查询并获取第一行结果
	rc = sqlite3_step(stmt);
	if (rc == SQLITE_ROW)
	{
		const unsigned char *text = sqlite3_column_text(stmt, 0);
		if (text == NULL)
		{
			sqlite3_finalize(stmt);
			return -1;
		}

		text_len = strlen((const char *)text);
		if (text_len >= buflen)
		{
			log_error("Buffer too small to hold data (%zu bytes needed)", text_len + 1);
			sqlite3_finalize(stmt);
			return -1;
		}

		strncpy(buf, (const char *)text, buflen - 1);
		buf[buflen - 1] = '\0';  // 确保字符串结尾
		sqlite3_finalize(stmt);
		return 0;  // 成功读取一条数据
	}
	if (rc == SQLITE_DONE)    //没有数据
	{
		sqlite3_finalize(stmt);
		return -1;
	}
	else	//查询出错
	{
		log_error("SQL error during step: %s", sqlite3_errmsg(db));
		sqlite3_finalize(stmt);
		return -1;
	}
}

/* 
int sqlite_has_data(sqlite3 *db)
{
	const char *sql = "SELECT COUNT(id) FROM clienttable;";
	int count = 0;
	char *err_msg = NULL;
				   
	int callback(void *count_ptr, int argc, char **argv, char **col_name)
	{
		*((int *)count_ptr) = atoi(argv[0]);
		return 0;
	}

	int rc = sqlite3_exec(db, sql, callback, &count, &err_msg);
	if (rc != SQLITE_OK)
	{
		log_error("SQL error: %s\n", err_msg);
		sqlite3_free(err_msg);
		return -1;
	}
	return count > 0 ? 1 : 0;
}

int sqlite_read_first_data(sqlite3 *db, char **result)
{
	const char *sql = "SELECT data FROM clienttable ORDER BY id ASC LIMIT 1;";
 	struct Data {
		char *value;
	} data = {NULL};

 	int callback(void *data_ptr, int argc, char **argv, char **col_name)
	{
		struct Data *d = (struct Data *)data_ptr;
		d->value = strdup(argv[0]); 
		return 0;
	}

	char *err_msg = NULL;
	int rc = sqlite3_exec(db, sql, callback, &data, &err_msg);
	if (rc != SQLITE_OK) 
	{
		log_error("SQL error: %s\n", err_msg);
		sqlite3_free(err_msg);
		return -1;
	}

	if (data.value == NULL)
	{
		return -1; 
	}

	*result = data.value;
	return 0;
}
*/

int sqlite_delete_first_data(sqlite3 *db)
{
	const char *sql = "DELETE FROM clienttable WHERE id = (SELECT MIN(id) FROM clienttable);";
	char *err_msg = NULL;
	int rc = sqlite3_exec(db, sql, NULL, NULL, &err_msg);
	
	if (rc != SQLITE_OK)
	{
		log_error("SQL error: %s\n", err_msg);
		sqlite3_free(err_msg);
		return -1;
	}
	return 0;
}

int sqlite_close_db(sqlite3 *db)
{
	int rc = sqlite3_close(db);
	
	if (rc != SQLITE_OK)
	{
		log_error("Failed to close database: %s\n", sqlite3_errmsg(db));
		return -1;
	}
	log_info("Database closed successfully.\n");
	return 0;
}

